"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 创建行存压缩表，执行DDL和DML,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : @kyeleze
Description :
    1. 创建行存压缩表，执行DDL和DML
    2. 执行ddl sql
    3. 执行dml sql
    4. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 执行成功
    2. 成功
    3. 成功
    4. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 't_dbtime_test_014'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.default_support_extended_features = \
            self.common.show_param('support_extended_features')
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.primary_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

    def test_sql_dbtime(self):
        step = '----step1:创建行存压缩表，执行DDL和DML expect:执行成功----'
        self.log.info(step)
        res = self.primary_sh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'support_extended_features = on')
        self.assertTrue(res, '参数设置失败')
        res = self.primary_sh.restart_db_cluster()
        self.assertTrue(res, "重启失败")
        create_table = f"drop table if exists {self.table_name};" \
                       f"create table {self.table_name} (col_sint smallint," \
                       f"col_int integer, col_bint bigint, col_dec decimal," \
                       f"col_num numeric, col_rel  real," \
                       f"col_dou double precision, col_sser  smallserial," \
                       f"col_ser serial, col_bser  bigserial," \
                       f"col_mon money,col_var varchar(200)," \
                       f"col_char char(320), col_char2 char, col_text text," \
                       f"col_nvchar nvarchar2, " \
                       f"col_time1 timestamp with time zone," \
                       f"col_time2 timestamp without time zone," \
                       f"col_date date, col_time3 time without time zone," \
                       f"col_time4 time with time zone, " \
                       f"col_interval interval, col_clob clob," \
                       f"col_raw raw, col_bytea bytea, col_reltime reltime," \
                       f"col_point point, col_lseg lseg, col_box box," \
                       f"col_path path, col_polygon polygon, " \
                       f"col_circle circle, col_cidr cidr, col_inet inet," \
                       f"col_macaddr macaddr, col_bit bit(10)," \
                       f"col_bitvar bit varying, col_tsvector tsvector," \
                       f"col_tsquery tsquery, col_uuid uuid, col_json json," \
                       f"col_jsonb jsonb, col_xml xml)with (compresstype=2);"
        result = self.primary_sh.execut_db_sql(create_table,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result,
                      '执行失败' + step)

        step = '----step2:执行ddl sql expect:成功----'
        self.log.info(step)
        ddl_sql = f"create unique index {self.table_name}_idx1 on " \
                  f"{self.table_name} (col_bint);" \
                  f"alter table {self.table_name} add constraint " \
                  f"{self.table_name}_pkey  primary key (col_int);" \
                  f"drop index {self.table_name}_idx1;"
        result = self.primary_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.DROP_INDEX_SUCCESS_MSG, result,
                      '执行失败' + step)

        step = '----step3:执行dml sql expect:成功----'
        insert_sql = f"insert into {self.table_name} values(1,2,3,1,1,1,1,1," \
                     f"1,1,1,'1','1','1','1','1', date'2020-08-05'," \
                     f"date'2020-08-05',date'2020-08-05',date'2020-08-05'," \
                     f"'21:21:21 pst', interval '3 hours','1', " \
                     f"hextoraw('deadef'), e'\xdeadbeef','90',point '(1,1)'," \
                     f"lseg '[(1,1),(2,2)]', box '(1,1),(2,2)', " \
                     f"path '[(1,1),(2,2),(3,3)]', " \
                     f"polygon '(1,1),(2,2),(3,3)'," \
                     f"circle '<(1,1),2>', '192.168.100.128/25', " \
                     f"'192.168.0.1/24', '08:00:2b:01:02:03', b'1001110101'," \
                     f"b'10110010101110', " \
                     f"'$$the lexeme     contains spaces$$', " \
                     f"'fat & (rat | cat)', " \
                     f"'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', " \
                     f"'{{{{\\\"aa\\\":1}}}}'::json, " \
                     f"'{{{{\\\"bb\\\":2}}}}'::jsonb, " \
                     f"'<plant><common>blue gentian</common>" \
                     f"<botanical>gentiana</botanical><zone>4</zone>" \
                     f"<light>sun or shade</light><price>$8.56</price>" \
                     f"<availability>050299</availability></plant>');"
        result = self.primary_sh.execut_db_sql(insert_sql,
                                               sql_type=self.user_param)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step)

        dml_sql = f"select count(*) from {self.table_name};" \
                  f"update {self.table_name} set " \
                  f"col_char='abcdefghijklmnopqrstuvwxyz1234567890' " \
                  f"where col_sint=1;" \
                  f"delete from {self.table_name} where col_int=1;" \
                  f"drop table {self.table_name};"
        result = self.primary_sh.execut_db_sql(dml_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('1 row', result, '执行失败' + step)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create unique index {self.table_name}_idx1',
                    f'alter table {self.table_name}',
                    f'drop index {self.table_name}_idx1',
                    f'create table {self.table_name}',
                    f'insert into {self.table_name}',
                    f'select * from {self.table_name}',
                    f'update {self.table_name}',
                    f'drop table {self.table_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.primary_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        res = self.primary_sh.execute_gsguc(
            'set',
            self.constant.GSGUC_SUCCESS_MSG,
            f'support_extended_features = '
            f'{self.default_support_extended_features}')
        self.assertTrue(res, '参数设置失败')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

